﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using GenericTools;
using System.Data.OleDb;
using DataModels.Enumerations;
using DataModels.Common;
using System.Text.RegularExpressions;

namespace ReportingManagement
{
    public class ExcelTemplateCell
    {
        #region Constants
        const string START_MIN = "MIN(";
        const string START_MAX = "MAX(";
        const string START_AVERAGE = "AVERAGE(";
        const string OPENING_BRACKET = "(";
        const string CLOSING_BRACKET = ")";

        #endregion

        #region Class Variables
        private int m_CellID;
        private int m_SheetID;
        private int m_CellRowIndex;
        private int m_CellColumnIndex;
        private string m_IndicatorDataSource;

        private string m_ColumnHeader;
        private string m_RowHeader;
        private int m_ColumnHeaderIndex;
        private int m_RowHeaderIndex;
        private string m_CellAddress;

        private string m_Value;
        private bool m_Calculated = false;
        private bool m_Valid;
        private Dictionary<string, string> m_DataSource;
        private const string INVALID_OBJECT = @"Invalid object";
        private const string MISSING_DATASOURCE = @"Missing data source";

        private ExcelTemplateCellExtraCollection m_CellExtras = new ExcelTemplateCellExtraCollection();
        #endregion

        #region Constructors
        private ExcelTemplateCell(int cellID)
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    //command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT CellID, SheetID, CellRowIndex, CellColumnIndex, 
                IndicatorDataSource, ColumnHeader, RowHeader, ColumnHeaderIndex, 
                RowHeaderIndex, CellAddress FROM ExcelTemplateCells WHERE CellID = @CellID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@CellID";
            param.DbType = DbType.Int32;
            param.Value = cellID;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count == 1)
            {
                m_Valid = true;
                DataRow row = dataTable.Rows[0];
                m_CellID = cellID;
                m_SheetID = Int32.Parse(row["SheetID"].ToString());
                m_IndicatorDataSource = row["IndicatorDataSource"].ToString();

                m_RowHeaderIndex = Int32.Parse(row["RowHeaderIndex"].ToString());
                m_ColumnHeaderIndex = Int32.Parse(row["ColumnHeaderIndex"].ToString());
                m_RowHeader = row["RowHeader"].ToString();
                m_ColumnHeader = row["ColumnHeader"].ToString();
                
                m_CellAddress = row["CellAddress"].ToString();
                if (string.IsNullOrWhiteSpace(m_CellAddress))
                {
                    m_CellRowIndex = Int32.Parse(row["CellRowIndex"].ToString());
                    m_CellColumnIndex = Int32.Parse(row["CellColumnIndex"].ToString());
                    m_CellAddress = string.Format("{0}{1}", GetExcelColumnName(m_CellColumnIndex), m_CellRowIndex);
                }
                else
                {
                    m_CellRowIndex = ExtractRowNumberFromExcelAddress(m_CellAddress);
                    m_CellColumnIndex = ExtractColumnNumberFromExcelAddress(m_CellAddress);
                }
            }
            else m_Valid = false;
        }

        private ExcelTemplateCell() { }
        #endregion

        #region Public Methods
        public static ExcelTemplateCell CreateExcelTemplateCellFromDB(int cellID)
        {
            ExcelTemplateCell cell = new ExcelTemplateCell(cellID);
            if (cell.Valid) return cell;
            else return null;
        }

        public static ExcelTemplateCell CreateExcelTemplateCell(DataModels.Models.ExcelTemplateCell cell)
        {
            var c = new ExcelTemplateCell();
            c.m_CellID = cell.SheetID;
            c.m_SheetID = cell.SheetID;
            c.m_IndicatorDataSource = cell.IndicatorDataSource;
            c.m_ColumnHeader = cell.ColumnHeader;
            c.m_RowHeader = cell.RowHeader;
            c.m_ColumnHeaderIndex = cell.ColumnHeaderIndex;
            c.m_RowHeaderIndex = cell.RowHeaderIndex;
            c.m_CellAddress = cell.CellAddress;

            if (string.IsNullOrWhiteSpace(c.m_CellAddress))
            {
                c.m_CellRowIndex = cell.CellRowIndex.Value;
                c.m_CellColumnIndex = cell.CellColumnIndex.Value;
                c.m_CellAddress = string.Format("{0}{1}", GetExcelColumnName(c.m_CellColumnIndex), c.m_CellRowIndex);
            }
            else
            {
                c.m_CellRowIndex = ExtractRowNumberFromExcelAddress(c.m_CellAddress);
                c.m_CellColumnIndex = ExtractColumnNumberFromExcelAddress(c.m_CellAddress);
            }            

            c.m_Valid = true;

            c.m_CellExtras = new ExcelTemplateCellExtraCollection();
            foreach (var e in cell.ExcelTemplateCellExtras)
            {
                var extra = ExcelTemplateCellExtra.CreateExcelTemplateCellExtra(e);
                c.m_CellExtras.Add(extra);
            }

            return c;
        }

        /// <summary>
        /// Evaluates the numerical value of cell based on temporary datasource
        /// </summary>
        /// <param name="datasource">Data Source</param>
        /// <param name="targetTables">List of table names in the datasource that will be used in the evaluation</param>
        /// <returns></returns>
        public int GetValue(DataSet datasource, params string[] targetTables)
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);

            Dictionary<string, string> simplifiedDataSource = SimplifyDatasetIntoDictionary(datasource, targetTables);
            return GetValue(simplifiedDataSource);
        }

        /// <summary>
        /// Evaluates the numerical value of cell based on temporary simplified dictionary
        /// </summary>
        /// <param name="simplifiedDataSource">Dictionary containing the data</param>
        /// <returns></returns>
        public int GetValue(Dictionary<string, string> simplifiedDataSource)
        {
            int calculatedValue = 0;
            int currentValue = 0;
            string sValue = String.Empty;
            bool valueIsNumeric = false;
            string[] components = m_IndicatorDataSource.Split(',');
            ArithmeticOperation operation = ArithmeticOperation.ADD;
            foreach (string component in components)
            {
                switch (component)
                {
                    case "+":
                        operation = ArithmeticOperation.ADD;
                        break;

                    case "-":
                        operation = ArithmeticOperation.SUBTRACT;
                        break;

                    default:
                        if (simplifiedDataSource.ContainsKey(component))
                        {
                            sValue = simplifiedDataSource[component];
                            try
                            {
                                currentValue = Int32.Parse(sValue);
                                switch (operation)
                                {
                                    case ArithmeticOperation.ADD:
                                        calculatedValue += currentValue;
                                        break;

                                    case ArithmeticOperation.SUBTRACT:
                                        calculatedValue -= currentValue;
                                        break;
                                }
                                valueIsNumeric = true;
                            }
                            catch { }
                        }
                        break;
                }
            }
            if (valueIsNumeric) return calculatedValue;
            else return 0;   
        }

        public static int ExtractColumnNumberFromExcelAddress(string address)
        {
            if (string.IsNullOrEmpty(address)) throw new ArgumentNullException("columnName");

            string columnName = Regex.Replace(address, @"[\d]", "");
            columnName = columnName.ToUpperInvariant();
            int sum = 0;

            for (int i = 0; i < columnName.Length; i++)
            {
                sum *= 26;
                sum += (columnName[i] - 'A' + 1);
            }

            return sum;
        }

        public static int ExtractRowNumberFromExcelAddress(string address)
        {
            if (string.IsNullOrEmpty(address)) throw new ArgumentNullException("ExcelAddress");

            string result = Regex.Replace(address, @"[^\d]", "");
            int rowIndex = int.Parse(result);
            return rowIndex;
        }

        public static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }
        #endregion

        #region Private Methods
        // TODO: Revisit this codes, it may be source of negative values
        private void CalculateValue()
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);
            if (m_Calculated) return;
            if (m_DataSource == null) throw new InvalidOperationException(MISSING_DATASOURCE);

            int value = 0;
            int currentValue = 0;
            string sValue = String.Empty;
            bool valueIsNumeric = false;
            string[] components = m_IndicatorDataSource.Split(',');
            ArithmeticOperation operation = ArithmeticOperation.ADD;
            foreach (string component in components)
            {
                switch (component)
                {
                    case "+":
                        operation = ArithmeticOperation.ADD;
                        break;

                    case "-":
                        operation = ArithmeticOperation.SUBTRACT;
                        break;

                    default:
                        // Single indicator e.g. ANC03
                        // Aggregate function like MIN(ANC05,PCR06)
                        if (component.Contains(OPENING_BRACKET))
                        {
                            string formula = component.ToUpper();
                            string indicators = formula;
                            indicators = indicators.Replace(START_MIN, string.Empty);
                            indicators = indicators.Replace(START_MAX, string.Empty);
                            indicators = indicators.Replace(START_AVERAGE, string.Empty);
                            indicators = indicators.Replace(CLOSING_BRACKET, string.Empty);

                            var indicatorList = indicators.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
                            currentValue = EvaluateAggreateFunction(formula, indicatorList);
                            valueIsNumeric = true;
                        }
                        else
                        {
                            // Single indicator
                            if (m_DataSource.ContainsKey(component))
                            {
                                sValue = m_DataSource[component];
                                bool converted = int.TryParse(sValue, out currentValue);
                                if (converted) valueIsNumeric = true;
                                else valueIsNumeric = false;
                            }
                        }

                        if (valueIsNumeric)
                        {
                            switch (operation)
                            {
                                case ArithmeticOperation.ADD:
                                    value += currentValue;
                                    break;

                                case ArithmeticOperation.SUBTRACT:
                                    value -= currentValue;
                                    break;
                            }
                        }
                        break;
                }
            }
            if (valueIsNumeric) m_Value = value.ToString();
            else m_Value = sValue;
            m_Calculated = true;
        }

        private static string CalculateValue(string indicatorDefinition, Dictionary<string, string> dataSource)
        {
            //int value = 0;
            //int currentValue = 0;
            //string sValue = string.Empty;
            //bool valueIsNumeric = false;
            //string[] components = indicatorDefinition.Split(',');
            //ArithmeticOperation operation = ArithmeticOperation.ADD;
            //foreach (string component in components)
            //{
            //    switch (component)
            //    {
            //        case "+":
            //            operation = ArithmeticOperation.ADD;
            //            break;

            //        case "-":
            //            operation = ArithmeticOperation.SUBTRACT;
            //            break;

            //        default:
            //            // Single indicator e.g. ANC03
            //            // Aggregate function like MIN(ANC05,PCR06)
            //            if (component.Contains(OPENING_BRACKET))
            //            {
            //                string formula = component.ToUpper();
            //                string indicators = formula;
            //                indicators = indicators.Replace(START_MIN, string.Empty);
            //                indicators = indicators.Replace(START_MAX, string.Empty);
            //                indicators = indicators.Replace(START_AVERAGE, string.Empty);
            //                indicators = indicators.Replace(CLOSING_BRACKET, string.Empty);

            //                var indicatorList = indicators.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
            //                currentValue = EvaluateAggreateFunction(formula, indicatorList);
            //                valueIsNumeric = true;
            //            }
            //            else
            //            {
            //                // Single indicator
            //                if (m_DataSource.ContainsKey(component))
            //                {
            //                    sValue = m_DataSource[component];
            //                    bool converted = int.TryParse(sValue, out currentValue);
            //                    if (converted) valueIsNumeric = true;
            //                    else valueIsNumeric = false;
            //                }
            //            }

            //            if (valueIsNumeric)
            //            {
            //                switch (operation)
            //                {
            //                    case ArithmeticOperation.ADD:
            //                        value += currentValue;
            //                        break;

            //                    case ArithmeticOperation.SUBTRACT:
            //                        value -= currentValue;
            //                        break;
            //                }
            //            }
            //            break;
            //    }
            //}
            //if (valueIsNumeric) m_Value = value.ToString();
            //else m_Value = sValue;
            //m_Calculated = true;
            return null;
        }

        private int EvaluateAggreateFunction(string formula, string[] indicators)
        {
            int value = 0;
            int[] indicatorValues = new int[indicators.Length];
            for (int i = 0; i < indicators.Length; i++)
            {
                string indicator = indicators[i];
                bool indicatorValueIsMissing = true;
                
                if (m_DataSource.ContainsKey(indicator))
                {
                    indicatorValueIsMissing = false;
                    string sValue = m_DataSource[indicator];
                    int v;
                    bool converted = int.TryParse(sValue, out v);
                    if (!converted) v = 0;
                    indicatorValues[i] = v;
                }
                else 
                {
                    // This takes care of a little bit complex formulas like MAX(ANC01+MAT05|MAT05-PCR10)
                    // The indicator will contain just part of component separated by | eg. ANC01+MAT05
                    indicator = indicator.Replace("+", ",+,");
                    indicator = indicator.Replace("-", ",-,");
                    string[] components = indicator.Split(',');
                    int v = 0;
                    ArithmeticOperation nextOperation = ArithmeticOperation.ADD;

                    foreach (var shortcode in components)
                    {
                        if (shortcode == "+")
                        {
                            nextOperation = ArithmeticOperation.ADD;
                            continue;
                        }

                        if (shortcode == "-")
                        {
                            nextOperation = ArithmeticOperation.SUBTRACT;
                            continue;
                        }

                        if (m_DataSource.ContainsKey(shortcode))
                        {
                            indicatorValueIsMissing = false;
                            string sV = m_DataSource[shortcode];
                            int x;
                            bool converted = int.TryParse(sV, out x);
                            if (!converted) x = 0;

                            if (nextOperation == ArithmeticOperation.ADD) v += x;
                            if (nextOperation == ArithmeticOperation.SUBTRACT) v -= x;
                        }
                    }

                    indicatorValues[i] = v;
                }

                if (indicatorValueIsMissing) indicatorValues[i] = 0;
            }

            if (formula.StartsWith(START_MIN))
            {
                int min = indicatorValues[0];
                for (int i = 1; i < indicatorValues.Length; i++)
                {
                    if (indicatorValues[i] < min) min = indicatorValues[i];
                }
                value = min;
            }

            if (formula.StartsWith(START_MAX))
            {
                int max = indicatorValues[0];
                for (int i = 1; i < indicatorValues.Length; i++)
                {
                    if (indicatorValues[i] > max) max = indicatorValues[i];
                }
                value = max;
            }

            if (formula.StartsWith(START_AVERAGE))
            {
                decimal sum = 0;
                foreach (int i in indicatorValues) sum += i;
                decimal avg = sum / indicatorValues.Length;
                value = (int)avg;
            }
            return value;
        }


        private static Dictionary<string, string> SimplifyDatasetIntoDictionary(DataSet datasource, params string[] targetTables)
        {
            Dictionary<string, string> simplifiedDataSource = new Dictionary<string, string>();
            foreach (var tableName in targetTables)
            {
                DataTable dataTable = datasource.Tables[tableName];
                foreach (DataRow row in dataTable.Rows)
                {
                    string key = row[0].ToString(); // short code is index 0
                    string value = row[1].ToString();   // value is index 1
                    simplifiedDataSource[key] = value;
                }
            }
            return simplifiedDataSource;
        }
        #endregion

        #region Public Properties
        public int CellID
        {
            get
            {
                if (m_Valid) return m_CellID; 
                else throw new InvalidOperationException(INVALID_OBJECT);                
            }
        }

        public int SheetID
        {
            get
            {
                if (m_Valid) return m_SheetID;
                else throw new InvalidOperationException(INVALID_OBJECT);        
            }
        }

        public int CellRowIndex
        {
            get
            {
                if (m_Valid) return m_CellRowIndex;
                else throw new InvalidOperationException(INVALID_OBJECT);        
            }
        }

        public int CellColumnIndex
        {
            get
            {
                if (m_Valid) return m_CellColumnIndex;
                else throw new InvalidOperationException(INVALID_OBJECT);        
            }
        }

        public string CellAddress
        {
            get
            {
                if (m_Valid) return m_CellAddress;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public int RowHeaderIndex
        {
            get
            {
                if (m_Valid) return m_RowHeaderIndex;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public int ColumnHeaderIndex
        {
            get
            {
                if (m_Valid) return m_ColumnHeaderIndex;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public string IndicatorDataSource
        {
            get
            {
                if (m_Valid) return m_IndicatorDataSource;
                else throw new InvalidOperationException(INVALID_OBJECT);        
            }
        }

        public string RowHeader
        {
            get
            {
                if (m_Valid) return m_RowHeader;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public string ColumnHeader
        {
            get
            {
                if (m_Valid) return m_ColumnHeader;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public bool Valid
        {
            get
            {
                return m_Valid;
            }
        }

        public ExcelTemplateCellExtraCollection CellExtras
        {
            get
            {
                return m_CellExtras;
            }
        }

        public string Value
        {
            get
            {
                if (m_Valid)
                {
                    if (!m_Calculated) CalculateValue();
                    return m_Value;
                }
                else throw new InvalidOperationException(INVALID_OBJECT);  
            }
            set
            {
                m_Value = value; //TODO: I don't like at all, not a bit...
                m_Calculated = true;
            }
        }

        public Dictionary<string, string> DataSource
        {
            get
            {
                return m_DataSource;
            }
            set
            {
                m_DataSource = value;
                m_Calculated = false;
            }
        }
        #endregion
    }

    public class ExcelTemplateCellCollection : List<ExcelTemplateCell>
    {

    }
}
